﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using BCCommon.Enums;
using BCEntity.Common.Payment.Payment;
using BCCommon.PaymentEnums;
using BCCommon;

namespace BCData.Common.Payment.Payment
{
    public class PaymentRecordData : IPaymentRecordData
    {
        private const string SQL_INSERT_ITEM = @"INSERT INTO PaymentRecord (PaymentChannelId, OrderType,Subject, ReferenceNumber, Amount, UserId, Status, CreateTime,AppType,OSType,Payer,AdminId,AdminFullName,FlowDirection,OperationType,AppId)
                                                 VALUES (?PaymentChannelId, ?OrderType,?Subject, ?ReferenceNumber, ?Amount, ?UserId, ?Status, ?CreateTime,?AppType,?OSType,?Payer,?AdminId,?AdminFullName,?FlowDirection,?OperationType,?AppId);
                                                 SELECT PaymentRecordId,PaymentChannelId,OrderType,AppType,OSType,Subject,ReferenceNumber,Amount,UserId,Status,CreateTime,SuccessTime,ReconciliationTime,FailRemark,
                                                        IsSettled,SettleDate,Discount,CheckSignTime,Payer,AdminId,AdminFullName,FlowDirection,OperationType,AppId,IsRefund
                                                 FROM PaymentRecord
                                                 WHERE PaymentRecordId = LAST_INSERT_ID()";
        private const string SQL_UPDATE_ITEM_BY_PAYMENTRECORDID = @"UPDATE PaymentRecord
                                                                    SET Status = ?Status,
                                                                        FailRemark=?FailRemark
                                                                    WHERE PaymentRecordId = ?PaymentRecordId;
                                                                    SELECT PaymentRecordId,PaymentChannelId,OrderType,AppType,OSType,Subject,ReferenceNumber,Amount,UserId,Status,CreateTime,SuccessTime,ReconciliationTime,FailRemark,
                                                                            IsSettled,SettleDate,Discount,CheckSignTime,Payer,AdminId,AdminFullName,FlowDirection,OperationType,AppId,IsRefund
                                                                    FROM PaymentRecord
                                                                    WHERE PaymentRecordId = ?PaymentRecordId";
        private const string SQL_UPDATE_ITEM_BY_PAYMENTRECORDID_OF_SUCCESSTIME = @"UPDATE PaymentRecord
                                                                                   SET Status = ?Status, SuccessTime = ?SuccessTime
                                                                                   WHERE PaymentRecordId = ?PaymentRecordId;
                                                                                   SELECT PaymentRecordId,PaymentChannelId,OrderType,AppType,OSType,Subject,ReferenceNumber,Amount,UserId,Status,CreateTime,SuccessTime,ReconciliationTime,FailRemark,
                                                                                        IsSettled,SettleDate,Discount,CheckSignTime,Payer,AdminId,AdminFullName,FlowDirection,OperationType,AppId,IsRefund
                                                                                   FROM PaymentRecord
                                                                                   WHERE PaymentRecordId = ?PaymentRecordId";
        private const string SQL_UPDATE_ITEM_BY_PAYMENTRECORDID_OF_SIGNTIME = @"UPDATE PaymentRecord 
                                                                                   SET CheckSignTime = ?CheckSignTime
                                                                                   WHERE PaymentRecordId = ?PaymentRecordId;
                                                                                   SELECT PaymentRecordId,PaymentChannelId,OrderType,AppType,OSType,Subject,ReferenceNumber,Amount,UserId,Status,CreateTime,SuccessTime,ReconciliationTime,FailRemark,
                                                                                            IsSettled,SettleDate,Discount,CheckSignTime,Payer,AdminId,AdminFullName,FlowDirection,OperationType,AppId,IsRefund
                                                                                   FROM PaymentRecord
                                                                                   WHERE PaymentRecordId = ?PaymentRecordId";
        private const string SQL_SELECT_ITEM_BY_PAYMENTRECORDID = @"SELECT PaymentRecordId,PaymentChannelId,OrderType,AppType,OSType,Subject,ReferenceNumber,Amount,UserId,Status,CreateTime,SuccessTime,ReconciliationTime,
                                                                            IsSettled,SettleDate,Discount,CheckSignTime,Payer,AdminId,AdminFullName,FlowDirection,OperationType,AppId,IsRefund
                                                                    FROM PaymentRecord
                                                                    WHERE PaymentRecordId = ?PaymentRecordId";
        private const string SQL_SELECT_ITEMS_BY_ORDERTYPE_REFERENCENUMBER = @"SELECT PaymentRecordId,PaymentChannelId,OrderType,AppType,OSType,Subject,ReferenceNumber,Amount,UserId,Status,CreateTime,SuccessTime,ReconciliationTime,FailRemark,
                                                                                    IsSettled,SettleDate,Discount,CheckSignTime,Payer,AdminId,AdminFullName,FlowDirection,OperationType,AppId,IsRefund
                                                                               FROM PaymentRecord";

        private const string SQL_SELECT_ITEMS_BY_USERID = @"SELECT PaymentRecordId,PaymentChannelId,OrderType,AppType,OSType,Subject,ReferenceNumber,Amount,UserId,Status,CreateTime,FailRemark,
                                                                    SuccessTime,ReconciliationTime,IsSettled,SettleDate,Discount,CheckSignTime,Payer,AdminId,AdminFullName,FlowDirection,OperationType,AppId,IsRefund
                                                            FROM PaymentRecord";

        private const string QUERY = @"SELECT A.PaymentRecordId,A.PaymentChannelId,A.OrderType,A.AppType,A.OSType,A.Subject,A.ReferenceNumber,A.Amount,A.UserId,A.Status,A.CreateTime,A.OperationType,A.AppId,A.FailRemark,
                                                A.SuccessTime,A.ReconciliationTime,A.IsSettled,A.SettleDate,A.Discount,A.CheckSignTime,A.Payer,B.UserName,B.Phone,C.Balance,A.AdminId,A.AdminFullName,A.FlowDirection,A.IsRefund
                                        FROM PaymentRecord A
                                        INNER JOIN `User` B ON A.UserId=B.UserId
                                        INNER JOIN Wallet C ON B.UserId=C.UserId";

        protected readonly IDatabaseContext databaseContext;

        public PaymentRecordData(IDatabaseContext databaseContext)
        {
            this.databaseContext=databaseContext;
        }

        public PaymentRecordEntity Create(PaymentChannelType payChannelType, string orderType,string subject, string referenceNumber, decimal amount, long? userId, SourceAppType appType,
            SourceOSType osType, SourcePayerType? payer, string appId = null, long? adminId=null,string adminFullName=null,string operationType=null, SourceFlowDirectionType? flowDirection= SourceFlowDirectionType.Income)
        {
            DynamicParameters parameters = new DynamicParameters();

            parameters.Add("?PaymentChannelId", payChannelType.ToString(), DbType.AnsiString, ParameterDirection.Input, 20);
            parameters.Add("?OrderType", orderType, DbType.AnsiString, ParameterDirection.Input, 50);
            parameters.Add("?Subject", subject, DbType.AnsiString, ParameterDirection.Input, 300);
            parameters.Add("?ReferenceNumber", referenceNumber, DbType.AnsiString, ParameterDirection.Input, 50);
            parameters.Add("?Amount", amount, DbType.Decimal);
            parameters.Add("?UserId", userId, DbType.Int64);
            parameters.Add("?Status", PaymentResultType.Pending.ToString(), DbType.AnsiString, ParameterDirection.Input, 20);
            parameters.Add("?CreateTime", DateTime.Now, DbType.DateTime);
            parameters.Add("?AppType", appType.ToString(), DbType.AnsiString);
            parameters.Add("?OSType", osType.ToString(), DbType.AnsiString);
            parameters.Add("?Payer", payer, DbType.Int32);
            parameters.Add("?AdminId", adminId, DbType.Int64);
            parameters.Add("?AdminFullName", adminFullName, DbType.AnsiString,size:20);
            parameters.Add("?FlowDirection", flowDirection.ToString(), DbType.AnsiString);
            parameters.Add("?OperationType", operationType?.Trim(), DbType.AnsiString);
            parameters.Add("?AppId", appId?.Trim(), DbType.AnsiString);

            return this.databaseContext.Master.QueryEntity<PaymentRecordEntity>(SQL_INSERT_ITEM, parameters, databaseContext.Transaction);
        }
        public PaymentRecordEntity SetAsRequireAction(long paymentRecordId)
        {
            DynamicParameters parameters = new DynamicParameters();

            parameters.Add("?Status", PaymentResultType.RequireAction.ToString(), DbType.AnsiString, ParameterDirection.Input, 20);
            parameters.Add("?PaymentRecordId", paymentRecordId, DbType.Int64);
            parameters.Add("?FailRemark", string.Empty, DbType.AnsiString, size: 200);
            return this.databaseContext.Master.QueryEntity<PaymentRecordEntity>(SQL_UPDATE_ITEM_BY_PAYMENTRECORDID, parameters, databaseContext.Transaction);
        }

        public PaymentRecordEntity SetAsSuccess(long paymentRecordId)
        {
            DynamicParameters parameters = new DynamicParameters();

            parameters.Add("?Status", PaymentResultType.Success.ToString(), DbType.AnsiString, ParameterDirection.Input, 20);
            parameters.Add("?SuccessTime", DateTime.Now, DbType.DateTime);
            parameters.Add("?PaymentRecordId", paymentRecordId, DbType.Int64);

            return this.databaseContext.Master.QueryEntity<PaymentRecordEntity>(SQL_UPDATE_ITEM_BY_PAYMENTRECORDID_OF_SUCCESSTIME, parameters, databaseContext.Transaction);
        }
        public PaymentRecordEntity SetAsSignTime(long paymentRecordId)
        {
            DynamicParameters parameters = new DynamicParameters();

            parameters.Add("?CheckSignTime", DateTime.Now, DbType.DateTime);
            parameters.Add("?PaymentRecordId", paymentRecordId, DbType.Int64);

            return this.databaseContext.Master.QueryEntity<PaymentRecordEntity>(SQL_UPDATE_ITEM_BY_PAYMENTRECORDID_OF_SIGNTIME, parameters, databaseContext.Transaction);
        }
        public PaymentRecordEntity SetAsFailed(long paymentRecordId, string remark)
        {
            DynamicParameters parameters = new DynamicParameters();

            parameters.Add("?Status", PaymentResultType.Failed.ToString(), DbType.AnsiString, ParameterDirection.Input, 20);
            parameters.Add("?PaymentRecordId", paymentRecordId, DbType.Int64);
            parameters.Add("?FailRemark", remark, DbType.AnsiString, size: 200);
            return this.databaseContext.Master.QueryEntity<PaymentRecordEntity>(SQL_UPDATE_ITEM_BY_PAYMENTRECORDID, parameters, databaseContext.Transaction);
        }

        public PaymentRecordEntity SetAsToBeConfirmed(long paymentRecordId)
        {
            DynamicParameters parameters = new DynamicParameters();

            parameters.Add("?Status", PaymentResultType.ToBeConfirmed.ToString(), DbType.AnsiString, ParameterDirection.Input, 20);
            parameters.Add("?PaymentRecordId", paymentRecordId, DbType.Int64);
            parameters.Add("?FailRemark", string.Empty, DbType.AnsiString, size: 200);

            return this.databaseContext.Master.QueryEntity<PaymentRecordEntity>(SQL_UPDATE_ITEM_BY_PAYMENTRECORDID, parameters, databaseContext.Transaction);
        }

        public PaymentRecordEntity GetPaymentRecordByPaymentRecordId(long paymentRecordId)
        {
            DynamicParameters parameters = new DynamicParameters();

            parameters.Add("?PaymentRecordId", paymentRecordId, DbType.Int64);

            return this.databaseContext.Slave.QueryEntity<PaymentRecordEntity>(SQL_SELECT_ITEM_BY_PAYMENTRECORDID, parameters);
        }

        public IEnumerable<PaymentRecordEntity> GetPaymentRecordsByReferenceNumber(FlowType? orderType, string referenceNumber)
        {
            DynamicParameters parameters = new DynamicParameters();
            List<string> wheres = new List<string>();
            wheres.Add("ReferenceNumber=?ReferenceNumber");
            parameters.Add("?ReferenceNumber", referenceNumber, DbType.AnsiString);
            if (orderType.HasValue)
            {
                wheres.Add("OrderType=?OrderType");
                parameters.Add("?OrderType", orderType.Value.ToString(), DbType.AnsiString);
            }
            string sql = string.Format("{0} {1}", SQL_SELECT_ITEMS_BY_ORDERTYPE_REFERENCENUMBER, wheres.Count > 0 ? "WHERE " + string.Join(" AND ", wheres) : string.Empty);
            return this.databaseContext.Slave.Query<PaymentRecordEntity>(sql, parameters);
        }



        /// <summary>
        /// 获取平台总交易金额
        /// </summary>
        /// <param name="provinceId">省份Id</param>
        /// <param name="start">开始时间</param>
        /// <param name="end">结束时间</param>
        /// <returns></returns>
        public decimal GetPaymentTotal(string provinceId, DateTime? start, DateTime? end)
        {
            DynamicParameters parameters = new DynamicParameters();
            StringBuilder sql = new StringBuilder();
            sql.AppendLine(@"SELECT Sum(A.Amount) FROM PaymentRecord A INNER JOIN Orders B ON A.ReferenceNumber = B.OrderNo WHERE A.Status='Success' ");
            if (string.IsNullOrWhiteSpace(provinceId))
            {
                sql.AppendLine("AND B.DepartProvinceId = ?DepartProvinceId");
                parameters.Add("?DepartProvinceId", provinceId, DbType.AnsiString, size: 20);
            }
            if (start.HasValue)
            {
                sql.AppendLine("AND B.CreateTime >= ?Start");
                parameters.Add("?Start", start.Value, DbType.DateTime);
            }
            if (end.HasValue)
            {
                sql.AppendLine("AND B.CreateTime <= ?End");
                parameters.Add("?End", end.Value, DbType.DateTime);
            }
            return this.databaseContext.Slave.ExecuteScalar<decimal>(sql.ToString(), parameters);
        }

        public IEnumerable<PaymentRecordEntity> GetPaymentRecordsByUserId(int pageIndex, int pageSize, out int dataCount, long userId)
        {

            string sql = string.Format(@"{0} WHERE UserId=?UserId AND Status='Success' Order By PaymentRecordId  DESC LIMIT {1},{2}; SELECT Count(1) FROM PaymentRecord WHERE UserId=?UserId  AND Status='Success'", SQL_SELECT_ITEMS_BY_USERID,
            (pageIndex == 0 ? 0 : pageIndex - 1) * pageSize,
             pageSize);

            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?UserId", userId, DbType.Int64, size: 20);

            var result = this.databaseContext.Slave.QueryMultiple<PaymentRecordEntity, int>(sql, parameters);
            dataCount = result.Item2;
            return result.Item1;
        }

        public async Task<PaymentRecordEntity> GetLatestByReferenceNumberAsync(string referenceNumber)
        {
            string sql =string.Format("{0} WHERE ReferenceNumber=?ReferenceNumber AND Status='Success' Order By  CreateTime desc LIMIT 0,1; ", SQL_SELECT_ITEMS_BY_ORDERTYPE_REFERENCENUMBER);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?ReferenceNumber", referenceNumber, DbType.AnsiString);
            return   await this.databaseContext.Slave.QueryFirstOrDefaultAsync<PaymentRecordEntity>(sql, parameters);
        }

        public async Task<IEnumerable<PaymentRecordEntity>> GetEntityByReferenceNumberAsync(string referenceNumber)
        {
            string sql = string.Format("{0} WHERE ReferenceNumber=?ReferenceNumber Order By  CreateTime desc LIMIT 0,1; ", SQL_SELECT_ITEMS_BY_ORDERTYPE_REFERENCENUMBER);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?ReferenceNumber", referenceNumber, DbType.AnsiString);
            return await this.databaseContext.Slave.QueryAsync<PaymentRecordEntity>(sql, parameters);
        }

        public async Task<(IEnumerable<PaymentRecordUserViewEntity>, int)> Query(string userName, string phone, SourceAppType? appType, FlowType? orderType, PaymentChannelType? payChannelType, DateTime? start, DateTime? end, int? pageIndex=1, int? pageSize=10, PaymentResultType? paymentStatus = PaymentResultType.Success)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            if (!string.IsNullOrWhiteSpace(userName))
            {
                wheres.Add("B.UserName LIKE CONCAT('%',CONCAT(?UserName,'%'))");
                parameters.Add("?UserName", userName.Trim(), DbType.AnsiString, size: 20);
            }
            if (!string.IsNullOrWhiteSpace(phone))
            {
                wheres.Add("B.Phone=?Phone");
                parameters.Add("?Phone", phone.Trim(), DbType.AnsiString, size: 20);
            }
            if (appType.HasValue)
            {
                wheres.Add("A.AppType=?AppType");
                parameters.Add("?AppType", appType.ToString(), DbType.AnsiString);
            }
            if (paymentStatus.HasValue)
            {
                wheres.Add("A.Status=?Status");
                parameters.Add("?Status", paymentStatus.ToString(), DbType.AnsiString);
            }
            if (orderType.HasValue)
            {
                wheres.Add("A.OrderType=?OrderType");
                parameters.Add("?OrderType", orderType.ToString(), DbType.AnsiString);
            }
            if (payChannelType.HasValue)
            {
                wheres.Add("A.PaymentChannelId=?PaymentChannelId");
                parameters.Add("?PaymentChannelId", payChannelType.ToString(), DbType.AnsiString);
            }
            if (start.HasValue && !end.HasValue)
            {
                wheres.Add("A.SuccessTime>=?Start");
                parameters.Add("?Start", start.Value, DbType.DateTime);
            }
            else if (!start.HasValue && end.HasValue)
            {
                wheres.Add("A.SuccessTime<=?End");
                parameters.Add("?End", end.Value, DbType.DateTime);
            }
            else if (start.HasValue && end.HasValue)
            {
                wheres.Add("A.SuccessTime>=?Start AND SuccessTime<=?End");
                parameters.Add("?Start", start.Value, DbType.DateTime);
                parameters.Add("?End", end.Value, DbType.DateTime);
            }
            string sql = string.Format(@"{0} {1} ORDER BY A.SuccessTime DESC LIMIT {2},{3};
                        SELECT COUNT(1) 
                            FROM PaymentRecord A
                            INNER JOIN `User` B ON A.UserId=B.UserId
                            INNER JOIN Wallet C ON B.UserId=C.UserId {1}",
              QUERY,
              wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : "",
              (pageIndex == 0 ? 0 : pageIndex - 1) * pageSize,
              pageSize);

            return await this.databaseContext.Slave.QueryMultipleAsync<PaymentRecordUserViewEntity, int>(sql, parameters);
        }

        public async Task<PaymentRecordBillViewEntity> GetSuccessByReferenceNumberAsync(string referenceNumber)
        {
            string sql = string.Format("{0} WHERE ReferenceNumber=?ReferenceNumber AND Status='Success' ORDER BY CreateTime DESC LIMIT 1; ", QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?ReferenceNumber", referenceNumber, DbType.AnsiString);
            return await this.databaseContext.Slave.QueryFirstOrDefaultAsync<PaymentRecordBillViewEntity>(sql, parameters);
        }

        public async Task<PaymentRecordBillViewEntity> GetNoRefundSuccessByReferenceNumberAsync(string referenceNumber)
        {
            string sql = string.Format("{0} WHERE ReferenceNumber=?ReferenceNumber AND Status='Success' AND OperationType IS NULL ORDER BY CreateTime DESC LIMIT 1; ", QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?ReferenceNumber", referenceNumber, DbType.AnsiString);
            return await this.databaseContext.Slave.QueryFirstOrDefaultAsync<PaymentRecordBillViewEntity>(sql, parameters);
        }

        public async Task<PaymentRecordEntity> UpdateRefundSuccess(long paymentRecordId)
        {
            string sql = SqlCommands.GetUpdateSql("PaymentRecord", new string[] { "IsRefund" }, new string[] { "PaymentRecordId" });
            sql = string.Format("{0} {1} WHERE PaymentRecordId=?PaymentRecordId", sql, QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?IsRefund", true, DbType.Boolean);
            parameters.Add("?PaymentRecordId", paymentRecordId, DbType.Int64,size:20);
            return await this.databaseContext.Slave.QueryFirstOrDefaultAsync<PaymentRecordBillViewEntity>(sql, parameters);
        }

        public async Task<PaymentRecordEntity> GetRefundPaymentRecord(string referenceNumber)
        {
            string sql = string.Format("{0} WHERE ReferenceNumber=?ReferenceNumber AND OperationType='Refund' AND IsRefund IS NULL ORDER BY CreateTime DESC LIMIT 1;", QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?ReferenceNumber", referenceNumber, DbType.AnsiString);
            return await this.databaseContext.Slave.QueryFirstOrDefaultAsync<PaymentRecordEntity>(sql, parameters);
        }

        public async Task<bool> UpdateSuccessByIdAsync(long paymentRecordId)
        {
            string sql = SqlCommands.GetUpdateSql("PaymentRecord", new string[] { "Status", "SuccessTime" }, new string[] { "PaymentRecordId" });
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?Status", PaymentResultType.Success.ToString(), DbType.AnsiString);
            parameters.Add("?SuccessTime", DateTime.Now, DbType.DateTime);
            parameters.Add("?PaymentRecordId", paymentRecordId, DbType.Int64);
            return await this.databaseContext.Master.ExecuteAsync(sql, parameters, databaseContext.Transaction) > 0;
        }

        public async Task<IEnumerable<PaymentRecordEntity>> GetPaymentRecordsByReferenceNumber(string referenceNumber, PaymentChannelType paymentChannelType)
        {
            string sql = string.Format("{0} WHERE ReferenceNumber=?ReferenceNumber AND PaymentChannelId=?PaymentChannelId", SQL_SELECT_ITEMS_BY_ORDERTYPE_REFERENCENUMBER);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?ReferenceNumber", referenceNumber, DbType.AnsiString);
            parameters.Add("?PaymentChannelId", paymentChannelType.ToString(), DbType.AnsiString);
            return await this.databaseContext.Slave.QueryAsync<PaymentRecordEntity>(sql, parameters);
        }

        public async Task<PaymentRecordEntity> UpdateRefundOperationType(long paymentRecordId)
        {
            string sql = SqlCommands.GetUpdateSql("PaymentRecord", new string[] { "OperationType" }, new string[] { "PaymentRecordId" });
            sql = string.Format("{0} {1} WHERE PaymentRecordId=?PaymentRecordId", sql, QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?OperationType", PaymentOperationType.Refund.ToString(), DbType.AnsiString);
            parameters.Add("?PaymentRecordId", paymentRecordId, DbType.Int64, size: 20);
            return await this.databaseContext.Master.QueryFirstOrDefaultAsync<PaymentRecordBillViewEntity>(sql, parameters, databaseContext.Transaction);
        }

        public async Task<IEnumerable<PaymentRecordEntity>> GetListByReferenceNumberAsync(List<string> referenceNumberList)
        {
            string sql = @"SELECT A.PaymentRecordId,A.PaymentChannelId,A.OrderType,A.AppType,A.OSType,A.Subject,A.ReferenceNumber,A.Amount,A.UserId,A.Status,A.CreateTime,A.SuccessTime,A.ReconciliationTime,
                        A.IsSettled,A.SettleDate,A.Discount,A.CheckSignTime,A.Payer,A.AdminId,A.AdminFullName,A.FlowDirection,A.OperationType,A.AppId,A.IsRefund
                        FROM PaymentRecord A
                        INNER JOIN
                        (SELECT ReferenceNumber,MAX(PaymentRecordId) AS PaymentRecordId 
                        FROM PaymentRecord 
                        WHERE FIND_IN_SET(ReferenceNumber,?ReferenceNumbers) 
                        AND OrderType IN ('FirstService','VehicleService') AND `Status` = 'Success' AND FlowDirection = 'InCome'
                        GROUP BY ReferenceNumber) B ON A.PaymentRecordId = B.PaymentRecordId";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("ReferenceNumbers", string.Join(",", referenceNumberList), DbType.AnsiString);
            return await this.databaseContext.Slave.QueryAsync<PaymentRecordEntity>(sql, parameters);
        }

        public IEnumerable<PaymentRecordEntity> GetPaymentRecordsByAgentRecordId(int pageIndex, int pageSize, out int dataCount,
            long agentRecordId)
        {
            string sql = string.Format(@"{0} WHERE ReferenceNumber=?ReferenceNumber AND Status='Success' Order By PaymentRecordId  DESC LIMIT {1},{2}; SELECT Count(1) FROM PaymentRecord WHERE UserId=?UserId  AND Status='Success'", SQL_SELECT_ITEMS_BY_USERID,
            (pageIndex == 0 ? 0 : pageIndex - 1) * pageSize,
             pageSize);

            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?ReferenceNumber", agentRecordId.ToString(), DbType.AnsiString, size: 20);

            var result = this.databaseContext.Slave.QueryMultiple<PaymentRecordEntity, int>(sql, parameters);
            dataCount = result.Item2;
            return result.Item1;
        }
    }
}
